from jirabl import *
from openpyxl import *
from openpyxl.utils import get_column_letter
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import PatternFill
from openpyxl.chart import PieChart, BarChart, Reference, Series
from shutil import copyfile
import os,re
import time
import io

#Environment Setting
dashboard_path = 'C:/Users/lacour3/JIRA/DASHBOARD/'
#dashboard_file= dashboard_path + 'A&S BB Dashboard.xlsx'

# release_info = '5G2??'

release_info = '2?R????'

class dashboard_worker():
    def __init__(self):
        if not os.path.isfile(dashboard_file):
            raise Exception('dashboard file not found', dashboard_file)
            
        self.workbook = load_workbook(dashboard_file)

        for sheet in self.workbook.worksheets:
            if sheet.title == 'Diagram':
                self.workbook.remove(self.workbook['Diagram'])
                
        self.workbook.create_sheet('Diagram')
        
        try:
            self.save()
        except IOError:
            print(time.strftime('\n%H:%M:%S', time.localtime()), release_info, 'A&S Dashboard generate Failed: Excel file is in using!')
            os._exit(0)
                
    class sheet_worker:
        def __init__(self, name, sheet):
            self.name = name
            self.sheet = sheet
            self.headers_row = 1
            self.first_row = 2
            self.headers = []
            
            i = 1
            while self.sheet.cell(self.headers_row, i).value:
                self.headers.append(str(self.sheet.cell(self.headers_row, i).value))
                i += 1
            
        def clean_dashboard(self):
            while self.sheet.cell(self.first_row, 1).value:
                self.sheet.delete_rows(self.first_row)

        def find_row(self, key:str):
            n = 1
            while self.sheet.cell(n,1).value:
                if self.sheet.cell(n,1).value == key:
                    return  n
                n = n + 1
            return n

        def find_col(self, key):
            n = 1
            while self.sheet.cell(1,n).value:
                if str(self.sheet.cell(1, n).value) == str(key):
                    return  n
                n = n + 1
            sheet.cell(1, n).value = key
            return n

        def dump_feature_info(self, feature_info):
            if feature_info == None:
                return
            
            row = self.find_row(feature_info['Feature ID'])

            for key in feature_info.keys():
                if key in self.headers:
                    col = self.find_col(key)
                    self.sheet.cell(row, col).value = feature_info[key]
                    self.sheet.cell(row, col).fill = self.set_color(self.sheet.cell(row, col).value)
        
        def aggregate_spec_status(self, fs1ta, cp1, cp2):
            if fs1ta == 'Obsolete' and cp1 == 'Obsolete' and cp2 == 'NA':
                return 'NA'
            
            if fs1ta == 'Obsolete' and cp1 == 'Obsolete' and cp2 == 'No Jira Item':
                return 'NA'

            if fs1ta in [None, 'No Jira Item']:
                return 'No FS1TA Item'
            elif fs1ta in ['Open']:
                return 'FS1TA Not Planned'
            elif fs1ta in ['In Progress', 'Scheduled']:
                return 'FS1TA Ongoing'
            elif cp1 in [None, 'No Jira Item', 'Open']:
                return 'FS1TA Done'
            elif cp1 in ['In Progress', 'Scheduled']:
                return 'CP1 Ongoing'
            elif cp2 in ['NA', 'Done']:
                return 'Done'
            elif cp2 in [None, 'No Jira Item', 'Open']:
                if cp1 == 'Obsolete':
                    return 'FS1TA Done'
                else:
                    return 'CP1 Done'
            else:
                spec_status = 'CP2 '+cp2
                return spec_status
            
        def get_spec_status(self, feature_id):
            row = self.find_row(feature_id)
            
            feature_id = self.sheet.cell(row, self.find_col('Feature ID')).value
            fs1ta_status = self.sheet.cell(row, self.find_col('FS1TA-Status')).value
            cp1_status = self.sheet.cell(row, self.find_col('CP1-Status')).value
            cp2_status = self.sheet.cell(row, self.find_col('CP2-Status')).value
                
            spec_status = self.aggregate_spec_status(fs1ta_status, cp1_status, cp2_status)
            
            return {'Feature ID': feature_id, 'Spec-Status':spec_status}

        def set_color(self, status):
            if status in ['Done', 'FS2 Approved']:
                return PatternFill("solid", fgColor="4EEE94")
            elif status in ['Obsolete', 'NA']:
                return PatternFill("solid", fgColor="D1D1D1")
            #elif status in ['In Progress', 'Scheduled']:
            #    return PatternFill("solid", fgColor="1E90FF")
            elif status in ['Open']:
                return PatternFill("solid", fgColor="EEB422")
            else:
                return PatternFill(None, fgColor="FFFFFF")

        def dump_ee_info(self, feature_id, ee_info_list):
            row = self.find_row(feature_id)

            for item in ee_info_list:
                col = self.find_col(item['CA'])

                if item['EE']:
                    effort = int(item['EE'])
                else:
                    effort = 0

                if self.sheet.cell(row, col).value:
                    self.sheet.cell(row, col).value = int(self.sheet.cell(row, col).value) + effort
                else:
                    self.sheet.cell(row, col).value = effort

                self.sheet.cell(row, col).fill = self.set_color(item['EE Status'])

        def get_fs_statistics(self):
            fs_dict = {'New':0, 'FS re-evaluation':0, 'FS0 Proposed':0, 'FS0 Approved':0, 'FS1 Proposed':0, 'FS1 Approved':0, 'FS2 Approved':0}
        
            fs_status_col = self.find_col('FS Status')
            row = 2
        
            while self.sheet.cell(row, 1).value:
                if self.sheet.cell(row, fs_status_col).value in fs_dict.keys():
                    fs_dict[self.sheet.cell(row, fs_status_col).value] += 1
            
                row += 1
            
            return fs_dict

        def get_fs1ta_statistics(self):
            fs1ta_dict = {'No Jira Item':0, 'Open':0, 'Scheduled':0, 'In Progress':0, 'Done':0, 'Obsolete':0}
        
            fs1ta_status_col = self.find_col('FS1TA-Status')
            row = 2
        
            while self.sheet.cell(row, 1).value:
                if self.sheet.cell(row, fs1ta_status_col).value in fs1ta_dict.keys():
                    fs1ta_dict[self.sheet.cell(row, fs1ta_status_col).value] += 1
            
                row += 1
            
            return fs1ta_dict
            
        def get_spec_statistics(self):
            spec_dict = {'New':0, 'FS1TA Ongoing':0, 'FS1TA Done':0, 'CP1 Open':0, 'CP1 Ongoing':0, 'CP1 Done':0, 'CP2 Ongoing':0, 'CP2 Partly Done':0, 'Done':0, 'NA':0}
            
            spec_status_col = self.find_col('Spec-Status')
            row = 2
            
            while self.sheet.cell(row, 1).value:
                if self.sheet.cell(row, spec_status_col).value in spec_dict.keys():
                    spec_dict[self.sheet.cell(row, spec_status_col).value] += 1
                    
                row += 1
                
            return spec_dict

    class diagram_worker:
        def __init__(self, name, sheet):
            self.name = name
            self.sheet = sheet
            self.date_col = 'A'
            self.chart_col = 'D'
            self.statistics_row = 2
            self.sheet.column_dimensions['A'].width = 15
            
        def draw_diagram(self, type):
            n = self.statistics_row + 1
            while self.sheet.cell(n, 1).value:
                n += 1
            
            if type == 'FS1TA Status':
                chart = PieChart()
            else:
                chart = BarChart()

            data = Reference(self.sheet, min_col=2, min_row=self.statistics_row+1, max_col = 2, max_row = n-1)
            chart.add_data(data)

            cats = Reference(self.sheet, min_col=1, min_row=self.statistics_row+1, max_row = n-1)
            chart.set_categories(cats)

            chart.title = type
            chart.overlap = 100
            chart.stype = 10
            chart.shape = 4
            chart.legend = None
        
            chart_pos = self.chart_col + str(self.statistics_row)
            self.sheet.add_chart(chart, chart_pos)

        def dump_diagram(self, data, type):
            self.sheet.cell(self.statistics_row, 1).value = type
            self.sheet.cell(self.statistics_row, 1).fill = PatternFill("solid", fgColor="D3D3D3")
            self.sheet.cell(self.statistics_row, 2).value = 'Number'
            self.sheet.cell(self.statistics_row, 2).fill = PatternFill("solid", fgColor="D3D3D3")

            rec_row = self.statistics_row + 1
            
            for key in data.keys():
                if data[key] != 0:
                    self.sheet.cell(rec_row, 1).value = key
                    self.sheet.cell(rec_row, 2).value = data[key]
                    rec_row += 1

            self.draw_diagram(type)
            
            self.statistics_row += 20
            
            return
            
    def save(self):
        self.workbook.save(dashboard_file)

class jira_worker():
    def __init__(self):
        self.jira = init_jira_connection()
        
        self.feature_info_field_list = [
            'customfield_37381',    # Feature ID
            'summary',              # summary
            'customfield_38716',    # Priority
            'customfield_38695',    # FOT Leader
            'customfield_38724',    # Planned System Release
            'customfield_38712',    # Requirement Area
            'customfield_38710',    # Target Releases
            'customfield_38705',    # FS Status and Date
            'customfield_43891',    # Requirement Area Owner
			'aggregatetimeoriginalestimate',    # Σ Original Estimate
			'aggregatetimeestimate' # Σ Remaining Estimate'
        ]
        
        self.ca_item_info_field_list = [
            'customfield_37381',    # Feature ID
            'summary',              # Summary
            'status',               # Status
            'customfield_38750',    # Active type
            'customfield_38753',    # sub-activity type
            'customfield_38698',    # Contact Person
            'customfield_38694',    # Start FB
            'customfield_38693',    # End FB
            'customfield_31591'     # Target End 
        ]

        self.ee_info_field_list = [
            'customfield_37381',    # Feature ID
            'status',               # Status
            'customfield_38690',    # Competence Area
            'customfield_38737',    # FS0 EE
            'customfield_38692'     # FS2 EE
        ]

        self.jql_feature_list = '"Target Releases" ~ "%s" AND type = "Customer Feature" AND (cf[38712] ~ "RadioPerf" OR cf[38712] ~ "QoSEndUserExp" OR cf[38712] ~ "Radio Performance" OR cf[38712] ~ "QoS, Services, End USer Experience" OR cf[38712] ~ "QoS & End User Experience") AND Status != OBSOLETE'%release_info
        self.jql_ca_list = 'issueFunction in portfolioChildrenOf(\' "Target Releases" ~ "%s" AND type = "Customer Feature" AND ( cf[38712] ~ "RadioPerf" OR cf[38712] ~ "QoSEndUserExp" OR cf[38712] ~ "Radio Performance" OR cf[38712] ~ "QoS, Services, End USer Experience" OR cf[38712] ~ "QoS & End User Experience")\') AND issuetype = "Competence Area" AND "sub-activity type" in (Pre-analysis,CP1,CP2) ORDER BY "Feature ID" ASC' %release_info
        self.jql_ee_list = 'issueFunction in portfolioChildrenOf(\'"Target Releases" ~ "%s" AND type = "Customer Feature" AND ( cf[38712] ~ "RadioPerf" OR cf[38712] ~ "QoSEndUserExp" OR cf[38712] ~ "Radio Performance" OR cf[38712] ~ "QoS, Services, End USer Experience" OR cf[38712] ~ "QoS & End User Experience")\') AND issuetype = "Effort Estimation" AND status not in (Obsolete) ORDER BY "Feature ID" ASC' %release_info

    def get_title(self, feature):
        match = re.compile('^(5GC00\d{4})\s{0,3}(.*)').search(get_summary(feature))
        
        if match:
            return match.groups()[1]

        match = re.compile('^(CB00\d{4})-5G (.*)').search(get_summary(feature))
            
        if match:
            return match.groups()[1]

        match = re.compile('^(CB00\d{4})-SR (.*)').search(get_summary(feature))
            
        if match:
            return match.groups()[1]

        return ''

    def get_feature_list(self):
        self.feature_list = []

        print(time.strftime("\n%H:%M:%S", time.localtime()), 'Get feature list ......')

        feature_list_jira = self.jira.search_issues(self.jql_feature_list, maxResults=0, fields=self.feature_info_field_list)

        for item in feature_list_jira:
            feature_info = {
                'Feature ID':       get_feature_id(item),
                'Summary':          self.get_title(item),
                'Priority':         get_priority(item),
                'FS Status':        get_fs_status(item),
                'Target Release':   getTargetSystemRelease(item),
                'Planned System Release': get_planned_system_release(item),
                'Requirement Area': get_requirement_area(item),
                'RAO':              get_requirement_area_owner(item),
                'FOT Leader':       get_fot_leader(item)
            }
                
            self.feature_list.append(feature_info)

    def get_ca_list(self):
        self.ca_list = {'fs1ta':[], 'cp1':[], 'cp2':[]}

        print(time.strftime("\n%H:%M:%S", time.localtime()), 'Get CA list from JIRA ......')

        ca_list_jira = self.jira.search_issues(self.jql_ca_list, maxResults=0, fields=self.ca_item_info_field_list)
            
        for item in ca_list_jira:
            if get_specification_type(item) == 'Pre-analysis':

                fs1ta_info = {
                    'Feature ID':       get_feature_id(item),
                    'Summary':          get_summary(item),
                    'FS1TA-Author':     get_contact_person(item),
                    'FS1TA-Status':     get_issue_status(item),
                    'FS1TA-End FB':     get_end_fb(item)
                }
                self.ca_list['fs1ta'].append(fs1ta_info)
            
            elif get_specification_type(item) == 'CP1':
                cp1_info = {
                    'Feature ID':       get_feature_id(item),
                    'Summary':          get_summary(item),
                    'CFAM-Author':      get_contact_person(item),
                    'CP1-Status':       get_issue_status(item),
                    'CP1-End FB':       get_end_fb(item)
                }
                self.ca_list['cp1'].append(cp1_info)
                
            elif get_specification_type(item) == 'CP2':
                cp2_info = {
                    'Feature ID':       get_feature_id(item),
                    'Summary':          get_summary(item),
                    'CFAM-Author':      get_contact_person(item),
                    'CP2-Status':       get_issue_status(item),
                    'CP2-End FB':       get_end_fb(item),
                    'CP2-Target End':   get_target_end(item)
                }
                self.ca_list['cp2'].append(cp2_info)
                
    def get_fs1ta_info(self, feature_id):
        fs1ta_info = {
            'Feature ID':       feature_id,
            'FS1TA-Author':     'No Jira Item',
            'FS1TA-Status':     'No Jira Item',
            'FS1TA-End FB':     'No Plan'
        }

        for item in self.ca_list['fs1ta']:
            if item['Feature ID'] == feature_id and \
                (fs1ta_info['FS1TA-Status'] in ['No Jira Item', 'Obsolete'] or \
                item['FS1TA-Status'] in ['In Progress', 'Scheduled', 'Open']):
                fs1ta_info['FS1TA-Status'] = item['FS1TA-Status']
                fs1ta_info['FS1TA-Author'] = item['FS1TA-Author']
                fs1ta_info['FS1TA-End FB'] = item['FS1TA-End FB']
               
        return fs1ta_info

    def get_cp1_info(self, feature_id):
        cp1_info = {
            'Feature ID':      feature_id,
            'CFAM-Author':     'No Jira Item',
            'CP1-Status':      'No Jira Item',
            'CP1-End FB':      'No Plan',
        }

        for item in self.ca_list['cp1']:
            if item['Feature ID'] == feature_id and \
                (cp1_info['CP1-Status'] in ['No Jira Item', 'Obsolete'] or \
                item['CP1-Status'] in ['In Progress', 'Scheduled', 'Open']):
                cp1_info['CP1-Status'] = item['CP1-Status']
                cp1_info['CFAM-Author'] = item['CFAM-Author']
                cp1_info['CP1-End FB'] = item['CP1-End FB']
               
        return cp1_info

    def parse_sub_cp2(self, feature_id, summary, status, end_fb, target_end):
        pattern_ignore1 = re.compile(r'5GC\d{6}-CFAM-CP2-[A-Z]-A[pP]h (.*)')
        pattern_ignore2 = re.compile(r'5GC\d{6}-[A-Z]-CFAM-CP2-A[pP]h (.*)')
        
        if pattern_ignore1.match(summary) or pattern_ignore2.match(summary):
            return None
                
        pattern = re.compile(r'(5GC|CB)\d{6}(-|-5G-|-SR-)CFAM-CP2 (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': 'CP2', 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-CFAM-CP2-[A-Z]{1,3} (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[3], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-(5G|SR)-CFAM-CP2-[A-Z]{1,3} (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[4], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-[A-Z]{1,3}-CFAM-CP2(.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[1], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}

        pattern = re.compile(r'(5GC|CB)\d{6}-(5G|SR)-[A-Z]{1,3}-CFAM-CP2(.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[2], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}

        pattern = re.compile(r'(5GC|CB)\d{6} - [A-Z] - CFAM - CP2 (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ',)[2], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
#new CP2 format without CFAM        

        pattern = re.compile(r'(5GC|CB)\d{6}(-|-5G-|-SR-)-CP2 (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': 'CP2', 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-CP2-[A-Z]{1,3} (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[3], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-(5G|SR)-CP2-[A-Z]{1,3} (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[4], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}
            
        pattern = re.compile(r'(5GC|CB)\d{6}-[A-Z]{1,3}-CP2(.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[1], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}

        pattern = re.compile(r'(5GC|CB)\d{6}-(5G|SR)-[A-Z]{1,3}-CP2(.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ', 1)[0].split('-')[2], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}

        pattern = re.compile(r'(5GC|CB)\d{6} - [A-Z]  - CP2 (.*)')
        if pattern.match(summary):
            return {'Sub-CP2': summary.split(' ',)[2], 'CP2-Status': status, 'CP2-End FB': end_fb, 'CP2-Target End': target_end}

        print('ATTENTION: Incorrect CFAM Summary Format, ', feature_id, ': ', summary)
        
        return None
        
    def get_cp2_info_list(self, feature_id):
        cp2_info_list = [{'Feature ID':feature_id}]
        
        for item in self.ca_list['cp2']:
            if item['Feature ID'] == feature_id:
                cp2_info = self.parse_sub_cp2(item['Feature ID'], item['Summary'], item['CP2-Status'], item['CP2-End FB'], item['CP2-Target End'])
                if cp2_info != None:
                    cp2_info_list.append(cp2_info)

        return cp2_info_list

    def append_sub_feature(self, ori, new):
        if ori == None:
            return new
        else:
            return ori + ',' + new

    def integrate_cp2_status(self, ori, new):
        if new == None:
            return ori
        elif new == 'Obsolete':
            if ori in [None, 'No Jira Item']:
                return 'NA'
            else:
                return ori
        elif new == 'Done':
            if ori in [None, 'NA', 'Done', 'No Jira Item']:
                return new
            else:
                return 'Partly Done'
        elif new == 'Open':
            if ori in [None, 'NA', 'No Jira Item']:
                return new
            elif ori in ['Done', 'Partly Done']:
                return 'Partly Done'
            else:
                return ori
        elif new == 'Scheduled':
            #print('\n bingo ')
            #print(ori)
            if ori in ['Done', 'Partly Done']:
                return 'Partly Done'
            else:
                return 'Scheduled'
        else:
            if ori in ['Done', 'Partly Done']:
                return 'Partly Done'
            else:
                return 'Ongoing'
    
    def aggregate_first_cp2(self, status, ori, new):
        if status in [None, 'Obsolete']:
            if ori == None:
                return 'NA'
            else:
                return ori
        elif new in [None, '']:
            return ori
        else:
            if ori in [None, 'NA']:
                return new
            elif int(ori) > int(new):
                return new
            else:
                return ori

    def aggregate_last_cp2(self, status, ori, new):
        if status in [None, 'Obsolete']:
            if ori == None:
                return 'NA'
            else:
                return ori
        elif new in [None, '']:
            return ori
        else:
            if ori in [None, 'NA']:
                return new
            elif int(ori) < int(new):
                return new
            else:
                return ori

    def aggregate_last_LE(self, status, ori, new):
        if status in [None, 'Obsolete']:
            if ori == None:
                return ''
            else:
                return ori
        elif new in [None, '']:
            return ori
        else:
            if ori in [None, '']:
                return new
            elif ori < new:
                return new
            else:
                return ori

    def aggregate_cp2_info(self, cp2_info_list):

        feature_id = cp2_info_list[0]['Feature ID']
        del cp2_info_list[0]
        
        cp2_info = {
            'Feature ID':      feature_id,
            'CP2-Status':      'No Jira Item',
            'Completed CP2':   None,
            'First CP2':       None,
            'Last CP2':        None,
            'Last LE Date':    None
        }

        for item in cp2_info_list:
            if item['CP2-Status'] == 'Done':
                cp2_info['Completed CP2'] = self.append_sub_feature(cp2_info['Completed CP2'], item['Sub-CP2'])

            if item['CP2-Status'] not in ['Done', 'Obsolete']:
                if item['CP2-End FB'] in cp2_info.keys():
                    cp2_info[item['CP2-End FB']] = self.append_sub_feature(cp2_info[item['CP2-End FB']], item['Sub-CP2'])
                else:
                    cp2_info[item['CP2-End FB']] = item['Sub-CP2']

            cp2_info['CP2-Status'] = self.integrate_cp2_status(cp2_info['CP2-Status'], item['CP2-Status'])
            cp2_info['First CP2'] = self.aggregate_first_cp2(item['CP2-Status'], cp2_info['First CP2'], item['CP2-End FB'])
            cp2_info['Last CP2'] = self.aggregate_last_cp2(item['CP2-Status'], cp2_info['Last CP2'], item['CP2-End FB'])
            cp2_info['Last LE Date'] = self.aggregate_last_LE(item['CP2-Status'], cp2_info['Last LE Date'], item['CP2-Target End'])

        
        return cp2_info

    def __competence_area_mapping(self, competence_area):
        if competence_area in ['RAN SysSpec1', '5GRAN SysSpecs', 'SRAN SysSpec','RAN SysSpec2']: 
            return 'A&S'
        elif competence_area[0:14] == 'RAN SysPec1':
            return 'A&S'
        elif competence_area in ['ECE L2 RT', 'ECE L2 NRT', '4G L2', '5G L2 PS','5G L2 LO', '5G L2 HI', '5G L2 EFS' ]:
            return 'L2'
        elif competence_area in ['ECE C-Plane RT', 'ECE C-Plane NRT', '4G C-Plane']:
            return 'L3'
        elif competence_area in ['ECE CU/DU test', '5G L2L3 Entity Test'] :
            return 'Entity Testing'
        elif competence_area in ['5GRAN I&V']:
            return 'SyVe'
        elif competence_area in ['ATF L1 Spec', 'ATF L1 SW for 5G', 'ATF L1 Platform', 'ATF L1 Productization', 'ATF_ARCH_5GMAX', 'ATF FDD UL PHY']:
            return 'L1'
        elif competence_area[0:2] == 'L1':
            return 'L1'
        elif competence_area in ['5G L3', '5G L3 EFS']:
            return 'L3'
        elif competence_area[0:3] in ['SoC', 'SOC']:
            return 'SoC'
        elif competence_area in ['NetAct-CM-PL', 'NetAct-FM','NetAct-Trace']:
            return 'BOAM'
        elif competence_area[0:4] in ['MANO', 'BOAM', 'BOAM_PIT','BOAM_ET','BOAM_BTS_SPF_LO', 'BOAM_OPERABILITY','BOAM_FRONTHAUL', 'BOAM_WEBEM', 'BOAM_EFS','BOAM_BTS_SPF_LO', 'BOAM_BASEBAND']:
            return 'BOAM'
        elif competence_area in ['ATF RCPESP', 'ATF RCPAS']:
            return 'RCP'
        elif competence_area[0:3] == 'RCP':
            return 'RCP'
        elif competence_area[0:2] == 'BB':
            return 'BBP'
        elif competence_area in ['E2E Transport_SW','E2E Transport_IV', 'E2E Transport_Spec']:
            return 'Trans'
        elif competence_area[0:2] == 'SC' or competence_area[0:4] == '5GSC':
            return 'Small Cell'
        elif competence_area in ['SR RFSW', 'RF SW']:
            return 'RF SW' 
        elif competence_area in ['RF I&V']:
            return 'RF I&V'
        elif competence_area in ['RF A&S', 'SR A&S', 'SR PRODZN', 'SR DFE', 'RF L1Low SW', 'BM DCC']:
            return 'RF'
        elif competence_area in ['SR RFHW', 'RF HW']:
            return 'RF HW'
        elif competence_area in ['5GRAN Airphone', '5GRAN Airphone L1']:
            return 'Airphone'
        elif competence_area in ['RAN RFS']:
            return 'RFS'
        elif competence_area in ['RadFld_FiVe_5G', 'RadFld_FiVe_CBTS']:
            return 'FiVe'
        elif competence_area in ['Radio System Performance Testing (PET)']:
            return 'PET'
        elif competence_area in ['5GRAN CN Emulator', '5GRAN xNB Simulator', 'RAN SysSimu', 'UEsim']:
            return 'Emulator'
        elif competence_area[0:8] == 'CloudRAN':
            return 'Cloud RAN'
        elif competence_area in ['Baseband', 'RIT_SIOP_IODT','4G RFfALU', '5GRAN CB', 'FHGW A&S', 'CDS SCM&CI', 'ECE System Specs', 'ECE Entity Specs', 'ECE SysSpecs Cloud', 'AirFrame CuDo', '5G CuDo', 'SRAN CuDo','ST_RRM']:
            return 'Others'
        elif competence_area[0:2] in  ['ST', '4G', 'RF','5G', 'SR', 'BT', 'AI', 'RA', 'L2','DC']:
            return 'Others'
        else:
            if competence_area != None and competence_area != '':
                print ('\nUnknown Competence Area: ', competence_area)
            return 'Others'

    def get_ee_list(self):
        self.ee_list = []

        ee_list_jira = self.jira.search_issues(self.jql_ee_list, maxResults=0, fields=self.ee_info_field_list)
        
        for item in ee_list_jira:
            ee_info = {
                'Feature ID':       get_feature_id(item),
                'EE Status':        get_issue_status(item),
                'CA':               getCompetenceArea(item),
                'FS0 EE':           get_fs0_ee(item),
                'FS2 EE':           get_fs2_ee(item)
            }

            self.ee_list.append(ee_info)

    def get_fs_ee_info(self, feature_id:str):
        total = 0
        ee_info_list = []

        total_status = None

        for item in self.ee_list:
            if feature_id == item['Feature ID']:
                if item['EE Status'] == 'Done':
                    fs_ee = item['FS2 EE']
                    total = total + int(fs_ee)
                    ee_status = 'Done'
                    
                    if total_status == None:
                        total_status = 'Done'
                else:
                    if item['FS2 EE'] != None:
                        fs_ee = item['FS2 EE']
                    elif item['FS0 EE'] != None:
                        fs_ee = item['FS0 EE']
                    else:
                        fs_ee = 0

                    ee_status = 'Open'
                    total_status = 'Open'
                    total = total + int(fs_ee)

                ee_info = {
                    'EE Status': ee_status,
                    'CA':   self.__competence_area_mapping(item['CA']),
                    'EE':   fs_ee
                }

                ee_info_list.append(ee_info)

        ee_info = {
            'EE Status': total_status,
            'CA':   'Total',
            'EE':   total
        }
        
        ee_info_list.append(ee_info)
        
        return ee_info_list

class log_worker():
    def __init__(self, file):
        self.file = io.open(file, 'w+')
        
    def close(self):
        self.file.close()

if __name__ == '__main__':
    
    print(time.strftime('%H:%M:%S', time.localtime()), 'Initial A&S RRM UP Dashboard ......')
    
    dashboard_file_tmp = dashboard_path + 'A&S RRM UP Dashboard.xlsx'
    dashboard_file= dashboard_path + 'A&S_RRM_UP_Dashboard_JIRA_%s.xlsx' %(time.strftime("%Y%m%d", time.localtime()))
    copyfile(dashboard_file_tmp, dashboard_file)
    # Initialize dashboard
    dashboard = dashboard_worker()
    
    dashboard_ee = dashboard_worker.sheet_worker('EE', dashboard.workbook['EE Dashboard'])
    dashboard_ee.clean_dashboard()
    
    dashboard_spec = dashboard_worker.sheet_worker('Spec', dashboard.workbook['Spec Dashboard'])
    dashboard_spec.clean_dashboard()
    
    dashboard_diagram = dashboard_worker.diagram_worker('Diagram', dashboard.workbook['Diagram'])
    
    # Create JIRA and Log object
    jira = jira_worker()

    # Get data from JIRA
    jira.get_feature_list()

    jira.get_ca_list()

    print(time.strftime("\n%H:%M:%S", time.localtime()), 'Get EE list from JIRA ......')
    jira.get_ee_list()
    
    # Generate Dashboard
    print(time.strftime("\n%H:%M:%S", time.localtime()), 'Generate Dashboard ......')
    for feature_item in jira.feature_list:
        # Feature Info
        dashboard_ee.dump_feature_info(feature_item)
        dashboard_spec.dump_feature_info(feature_item)

        # FS1TA Info
        dashboard_ee.dump_feature_info(jira.get_fs1ta_info(feature_item['Feature ID']))
        dashboard_spec.dump_feature_info(jira.get_fs1ta_info(feature_item['Feature ID']))
        
        # CP1 Info 
        dashboard_spec.dump_feature_info(jira.get_cp1_info(feature_item['Feature ID']))
    
        # CP2 Info
        cp2_info_list = jira.aggregate_cp2_info(jira.get_cp2_info_list(feature_item['Feature ID']))
        dashboard_spec.dump_feature_info(cp2_info_list)
        
        # Spec Status
        dashboard_spec.dump_feature_info(dashboard_spec.get_spec_status(feature_item['Feature ID']))

        # EE Info
        dashboard_ee.dump_ee_info(feature_item['Feature ID'], jira.get_fs_ee_info(feature_item['Feature ID']))

    # Generate Diagram
    print(time.strftime("\n%H:%M:%S", time.localtime()), 'Generate Diagram ......')
    
    dashboard_diagram.dump_diagram(dashboard_spec.get_fs_statistics(), 'FS Status')
    dashboard_diagram.dump_diagram(dashboard_spec.get_fs1ta_statistics(), 'FS1TA Status')
    dashboard_diagram.dump_diagram(dashboard_spec.get_spec_statistics(), 'Spec Status')
    
    # Close work
    dashboard.save()

    print(time.strftime('\n%H:%M:%S', time.localtime()), 'A&S UP Dashboard generate Successfully!')
